#coding=utf-8
import utils

create_price_sql = '''
create table if not exists price
    (id int not null primary key auto_increment,
    exday_id int not null unique,
    high decimal(9,3), low decimal(9,3), open decimal(9,3), close decimal(9,3), avg decimal(9,3),
    adj_close decimal(9,3), zd decimal(5,2),
    volume bigint, hs decimal(5,2) , amount bigint);
'''

create_everydayprice_sql = '''
create view everydayprice as 
    select scode.code, scode.name, exday.exdate, price.open, price.close, price.high, price.low,price.avg,
           price.zd, price.hs, price.volume, price.amount
    from scode, exday, price
    where scode.code = exday.code and exday.id = price.exday_id
'''

def get_price(con, code, exdate):
    c = con.cursor()
    
    c.execute("select high, low, open, close, adj_close, avg, zd, volume,amount,hs from price,exday where exday.code=%s and exday.exdate=%s and exday.id = price.exday_id", (code, exdate))
    r = c.fetchone()
    colname =[ i[0] for i in c.description]
    
    c.close()
    if not r:
        return None
    
    return dict(zip(colname,r))

def get_price_by_exday_id(con, i):
    
    c = con.cursor()
    
    c.execute("select high, low, open, close, adj_close, zd, volume from price where exday_id=%s", i)
    r = c.fetchone()
    colname =[ i[0] for i in c.description]
    
    c.close()
    if not r:
        return None
    
    return dict(zip(colname,r))
